package com.universest.swordholder.data.dao

import androidx.room.*
import kotlinx.coroutines.flow.Flow

@Entity(tableName = "passage")
data class Passage(
    @PrimaryKey(autoGenerate = true) val passageId: Int = 0,
    val contentId: Int = -1,
    val simpleContentId: Int = -1,
    val bookId: Int = -1,
    val title: String = "",
    val volumeId: Int = -1,
    val passageExtInfo: String = "",
    val order: Int = 0,
    val createTime: Long = System.currentTimeMillis(),
    val lastEditTime: Long = System.currentTimeMillis(),
    val charCount: Int = 0
)

@Dao
interface PassageDao {

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insert(passage: Passage): Long

    @Update
    suspend fun update(passage: Passage)

    @Delete
    suspend fun delete(passage: Passage)

    @Query("SELECT * FROM passage WHERE passageId = :passageId")
    fun getPassageById(passageId: Int): Flow<Passage?>

    @Query("SELECT * FROM passage WHERE bookId = :bookId ORDER BY [order]")
    fun getPassagesByBookId(bookId: Int): Flow<List<Passage>>

    @Query("SELECT * FROM passage WHERE bookId = :bookId AND volumeId = :volumeId ORDER BY [order]")
    fun getPassagesByBookIdAndVolumeId(bookId: Int, volumeId: Int): Flow<List<Passage>>

    @Query("SELECT * FROM passage WHERE bookId = :bookId AND volumeId = :volumeId ORDER BY [order] LIMIT 1")
    fun getFirstPassageByBookIdAndVolumeId(bookId: Int, volumeId: Int): Flow<Passage?>

    @Query("SELECT * FROM passage WHERE bookId = :bookId AND volumeId = :volumeId ORDER BY [order] DESC LIMIT 1")
    fun getLastPassageByBookIdAndVolumeId(bookId: Int, volumeId: Int): Flow<Passage?>

    @Query("SELECT * FROM passage")
    fun getAllPassages(): Flow<List<Passage>>

    @Query("DELETE FROM passage WHERE passageId = :passageId")
    suspend fun deleteById(passageId: Int)

    @Query("DELETE FROM passage WHERE bookId = :bookId")
    suspend fun deleteByBookId(bookId: Int)

    @Query("SELECT COUNT(*) FROM passage WHERE bookId = :bookId")
    fun getPassageCountByBookId(bookId: Int): Flow<Int>

    @Query("SELECT * FROM passage WHERE volumeId = :volumeId AND [order] > :order AND passageId != :exclude ORDER BY [order] LIMIT 1")
    fun getFirstPassageAfterOrderInVolume(volumeId: Int, order: Int,exclude: Int = -1): Flow<Passage?>

    @Query("SELECT * FROM passage WHERE volumeId = :volumeId AND [order] < :order AND passageId != :exclude ORDER BY [order] DESC LIMIT 1")
    fun getLastPassageBeforeOrderInVolume(volumeId: Int, order: Int,exclude: Int = -1): Flow<Passage?>

    @Query("SELECT * FROM passage WHERE volumeId = :volumeId AND passageId != :exclude ORDER BY [order] DESC LIMIT 1")
    fun getPassageWithMaxOrderInVolume(volumeId: Int,exclude: Int = -1): Flow<Passage?>

    @Query("SELECT * FROM passage WHERE volumeId = :volumeId AND passageId != :exclude ORDER BY [order] ASC LIMIT 1")
    fun getPassageWithMinOrderInVolume(volumeId: Int,exclude: Int = -1): Flow<Passage?>
}
